数据定义

默认值

一个列可以被分配一个默认值。当一个新行被创建且没有为某些列指定值时，这些列将会被它们相应的默认值填充。

如果没有显式指定默认值，则默认值是空值。

在一个表定义中，默认值被列在列的数据类型之后。例如：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric DEFAULT 9.99
5);

默认值可以是一个表达式，它将在任何需要插入默认值的时候被实时计算（*不*是表创建时）

一个常见的例子是为一个timestamp列指定默认值为CURRENT_TIMESTAMP，这样它将得到行被插入时的时间。

生成列

生成的列是一个特殊的列，它总是从其他列计算而来。

生成列有两种:存储列和虚拟列。

存储生成列在写入(插入或更新)时计算，并且像普通列一样占用存储空间。

虚拟生成列不占用存储空间并且在读取时进行计算。

PostgreSQL 目前只实现了存储生成列。

建立一个生成列，在 CREATE TABLE中使用 GENERATED ALWAYS AS 子句, 例如:

sql
1CREATE TABLE people (
2    ...,
3    height_cm numeric,
4    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
5);

必须指定关键字 STORED 以选择存储类型的生成列。

生成列不能被直接写入. 在INSERT 或 UPDATE 命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。

约束

约束弥补了数据类型所不能约束的情况，比如一个包含产品价格的列应该只接受正值。但是没有任何一种标准数据类型只接受正值。

检查约束

一个检查约束是最普通的约束类型。它允许我们指定一个特定列中的值必须要满足一个布尔表达式。例如，为了要求正值的产品价格，我们可以使用：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric CHECK (price > 0)
5);

约束定义就和默认值定义一样跟在数据类型之后。默认值和约束之间的顺序没有影响。

一个检查约束由关键字CHECK以及其后的包围在圆括号中的表达式组成。

我们也可以给与约束一个独立的名称。要指定一个命名的约束，请在约束名称标识符前使用关键词CONSTRAINT。然后把约束定义放在标识符之后（如果没有以这种方式指定一个约束名称，系统将会为我们选择一个）。

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric CONSTRAINT positive_price CHECK (price > 0)
5);

一个检查约束也可以引用多个列。例如我们存储一个普通价格和一个打折后的价格，而我们希望保证打折后的价格低于普通价格：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric CHECK (price > 0),
5    discounted_price numeric CHECK (discounted_price > 0),
6    CHECK (price > discounted_price)
7);

最后一行的约束使用了一种新的语法，它没有跟在特定的列，而是作为独立项出现在逗号分隔的列列表中。

我们将前两个约束称为列约束，而第三个约束为表约束。

列约束也可以写成表约束，但反过来不行。

将上面的例子改造成全使用表约束：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric,
5    CHECK (price > 0),
6    discounted_price numeric,
7    CHECK (discounted_price > 0),
8    CHECK (price > discounted_price)
9);

还可以这么写：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric CHECK (price > 0),
5    discounted_price numeric,
6    CHECK (discounted_price > 0 AND price > discounted_price)
7);

表约束也可以用列约束相同的方法来指定名称：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric,
5    CHECK (price > 0),
6    discounted_price numeric,
7    CHECK (discounted_price > 0),
8    CONSTRAINT valid_discount CHECK (price > discounted_price)
9);

注意：一个检查约束在其检查表达式值为真或者空时被满足。因为当任何操作数为空时大部分表达式将计算为空值，所以它们不会阻止被约束列中的空值。

非空约束

为了保证一个列不包含空值，可以使用非空约束。

一个非空约束仅仅指定一个列中不会有空值。语法例子：

sql
1CREATE TABLE products (
2    product_no integer NOT NULL,
3    name text NOT NULL,
4    price numeric
5);

非空约束总是被写成一个列约束。

但也可以用检查约束实现：

sql
1CHECK (column_name IS NOT NULL)

但在 PostgreSQL 中创建一个显式的非空约束更高效。

列也可以有多于一个的约束，只需要将这些约束全部写出来：

sql
1CREATE TABLE products (
2    product_no integer NOT NULL,
3    name text NOT NULL,
4    price numeric NOT NULL CHECK (price > 0)
5);

约束的顺序没有关系，因为并不需要决定约束被检查的顺序。

唯一约束

唯一约束保证在一列中或者一组列中保存的数据在表中所有行间是唯一的。写成一个列约束的语法是：

sql
1CREATE TABLE products (
2    product_no integer UNIQUE,
3    name text,
4    price numeric
5);

写成一个表约束的语法是：

sql
1CREATE TABLE products (
2    product_no integer,
3    name text,
4    price numeric,
5    UNIQUE (product_no)
6);

要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔：

sql
1CREATE TABLE example (
2    a integer,
3    b integer,
4    c integer,
5    UNIQUE (a, c)
6);

这指定这些列的组合值在整个表的范围内是唯一的，但其中任意一列的值并不需要是（一般也不是）唯一的。

我们可以通常的方式为一个唯一索引命名：

sql
1CREATE TABLE products (
2    product_no integer CONSTRAINT must_be_different UNIQUE,
3    name text,
4    price numeric
5);

通常，如果表中在约束所包括列上有超过一行的值相同，将会违反唯一约束。但是在这种比较中，两个空值被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。

主键 PRIMARY KEY

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。

定义一个主键：

sql
1CREATE TABLE products (
2    product_no integer PRIMARY KEY,
3    name text,
4    price numeric
5);

效果相当于UNIQUE+NOT NULL

sql
1CREATE TABLE products (
2    product_no integer UNIQUE NOT NULL,
3    name text,
4    price numeric
5);

主键也可以包含多于一个列，其语法和唯一约束相似：

sql
1CREATE TABLE example (
2    a integer,
3    b integer,
4    c integer,
5    PRIMARY KEY (a, c)
6);

一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。

关系数据库理论要求每一个表都要有一个主键。但 PostgreSQL 中并未强制要求这一点，但是最好能够遵循它。

外键

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。这维持了两个关联表之间的引用完整性。

假设我们有一个产品表：

sql
1CREATE TABLE products (
2    product_no integer PRIMARY KEY,
3    name text,
4    price numeric
5);

我们还有一个存储这些产品订单的表。我们希望保证订单表中只包含真正存在的产品的订单。因此我们在订单表中定义一个引用产品表的外键约束：

sql
1CREATE TABLE orders (
2    order_id integer PRIMARY KEY,
3    product_no integer REFERENCES products (product_no),
4    quantity integer
5);

现在就不可能创建包含不存在于产品表中的product_no值（非空）的订单。

在这种情况下，订单表是引用表而产品表是 ß被引用表。

相应地，也有引用和被引用列的说法。

上面的外键约束还有一个简写

sql
1CREATE TABLE orders (
2    order_id integer PRIMARY KEY,
3    product_no integer REFERENCES products,
4    quantity integer
5);

如果缺少列的列表，则被引用表的主键将被用作被引用列。

一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：

sql
1CREATE TABLE t1 (
2  a integer PRIMARY KEY,
3  b integer,
4  c integer,
5  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
6);

被约束列的数量和类型应该匹配被引用列的数量和类型。

一个表可以有超过一个外键约束。这被用于实现表之间的多对多关系。

现在假设我们有一个产品表和订单表，同时一个订单可以包含多个产品。现在我们创建三张表：

sql
1CREATE TABLE products (
2    product_no integer PRIMARY KEY,
3    name text,
4    price numeric
5);
6CREATE TABLE orders (
7    order_id integer PRIMARY KEY,
8    shipping_address text
9);
10CREATE TABLE order_items (
11    product_no integer REFERENCES products,
12    order_id integer REFERENCES orders,
13    quantity integer,
14    PRIMARY KEY (product_no, order_id)
15);

上面的代码我们让最后一张 order_items 表同时跟 products 表和 orders 表做外键连接。

order_items 表的 product_no 与 order_id 两者形成主键。

现在给每张表插入数据

sql
1INSERT INTO products(product_no,name,price) VALUES (1, '椅子', 50);
2INSERT INTO orders(order_id,shipping_address) VALUES (1, '上海');
3INSERT INTO order_items(product_no,order_id,quantity)VALUES(1,1,100)

我们已经知道外键不允许创建与产品和订单不相关的信息。

如果我已经有了订单，但是该产品被删除了，会怎么样？

SQL 允许我们处理这种情况：

1. 不允许删除被引用的产品
2. 同时删除引用产品的订单

由于我们创建表的时候已经创建了外键约束，所以首先要删除原来的外键约束：

sql
1ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey;
2ALTER TABLE order_items DROP CONSTRAINT order_items_product_no_fkey;

默认的 foreign_key的命名格式为：<table name>_<column_name>_fkey。

我们也可以在创建时指定外键名，例如：

sql
1CREATE TABLE order_item (
2    product_no integer CONSTRAINT products_no_fk REFERENCES products,
3    order_id integer CONSTRAINT order_id_fk REFERENCES orders,
4    quantity integer,
5    PRIMARY KEY (product_no, order_id)
6);

现在我们给order_items.product_no增加不允许删除被引用产品的策略。

sql
1ALTER TABLE order_items
2ADD CONSTRAINT fk_produc_no
3FOREIGN KEY (product_no)
4REFERENCES products (product_no)
5ON DELETE RESTRICT
6ON UPDATE RESTRICT;

再给order_items.order_id增加同时删除引用产品的订单的策略。

sql
1ALTER TABLE order_items
2ADD CONSTRAINT fk_order_id
3FOREIGN KEY (order_id)
4REFERENCES orders (order_id)
5ON DELETE CASCADE
6ON UPDATE CASCADE;

现在我要删除产品则会报错：

sql
1DELETE FROM products WHERE product_no =1;
2
3ERROR:  update or delete on table "products" violates foreign key constraint "order_items_product_no_fkey" on table "order_items"
4DETAIL:  Key (product_no)=(1) is still referenced from table "order_items".

如果删除 orders 表的数据，则会删除成功，并且将 order_items 表中关联的行一并删除。

sql
1DELETE FROM orders WHERE order_id =1;

下面我们分析一下几个关键字：

• DELETE 删除
• UPDATE 更新
• RESTRICT 阻止
• CASCADE 级联

DELETE RESTRICT 我们称为阻止删除，DELETE CASCADE 我们称为级联删除。这是两个比较常用的选项。

他们的区别在于DELETE RESTRICT不允许还有引用列时删除，而DELETE CASCADE则会随着被引用列的删除一起被删除。

UPDATE 操作时逻辑一致，只不过由删除变成了更新。

此外，还有几个关键字：

• NO ACTION 默认行为（例如更新或删除时发现有引用列则报错，跟 RESTRICT 的区别在于 NO ACTION 允许检查推迟到事务之后）
• SET NULL 引用列设置为空值。例如删除被引用列时，引用列变成空值。
• SET DEFAULT 引用列设置为默认值。例如删除被引用列时，引用列变成默认值。

一个外键所引用的列必须是一个主键或者被唯一约束所限制。

这意味着被引用列总是拥有一个索引（位于主键或唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查将会很高效。

排他约束

排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。

sql
1CREATE TABLE example (
2    id serial PRIMARY KEY,
3    range_start integer,
4    range_end integer,
5    EXCLUDE USING gist (int4range(range_start, range_end) WITH &&)
6);

在上面的示例中，我们创建了一个名为 example 的表，其中包含了两个整数列 range_start 和 range_end，并定义了一个排他约束，以确保任何两行的 int4range（整数范围）列值不会重叠。此约束使用 GiST 索引来检查是否存在重叠。

系统列

每一个表都有一些系统隐式定义的 system columns，用户不需要关心这些列，只需要知道他们存在即可。

• tableoid：包含这一行的表的 OID。该列是特别为从继承层次中选择的查询而准备
• xmin：插入该行版本的事务身份（事务 ID）。
• cmin：插入事务中的命令标识符（从 0 开始）。
• xmax：删除事务的身份（事务 ID），对于未删除的行版本为 0。
• cmax：删除事务中的命令标识符，或者为 0。
• ctid：行版本在其表中的物理位置。

extensions

Postgres 内置了不少扩展，可以通过以下命令查看：

sql
1SELECT * FROM pg_available_extensions;

有一个非常常用的扩展，用来生成 UUID_V4数据的，我们可以使用它来替代我们在写 SQL 时创建 uuid。

启用该扩展

sql
1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

开启插件后，可以看到 Functions 里面已经出现了不少函数了

现在我们使用它：

sql
1SELECT uuid_generate_v4();
2-- c6cbb3bb-06bb-481d-a412-ccff27094858

现在用 uuid 作为主键是不是特别方便了？

基于这一点，现在我们可以创建以 uuid 为主键的 table：

sql
1CREATE TABLE weather (
2	id UUID PRIMARY KEY,
3    city            varchar(80),
4    temp_lo         int,           -- 最低温度
5    temp_hi         int,           -- 最高温度
6    prcp            real,          -- 湿度
7    date            date
8);

并插入一条数据：

sql
1INSERT INTO weather (id,city, temp_lo, temp_hi, prcp, date)
2    VALUES (uuid_generate_v4(),'San Francisco', 43, 57, 0.0, '1994-11-29');

id	city	temp_lo	temp_hi	prcp	date
dc8cd58d-ae3c-4185-9b32-4c7baa9c1d36	San Francisco	43	57	0	1994-11-29

小结

• 数据定义时，我们可以指定默认值，如果没有显式指定，则默认值为 null。
• PostgreSQL 目前只实现了存储生成列。生成列不能被直接指定，而是从其他的列计算而来的。
• 约束弥补了数据类型定义所不能实现的功能，这一章学习到的约束为：
  1. 检查约束：允许我们指定一个特定列中的值必须要满足一个布尔表达式。
  2. 非空约束：NOT NULL
  3. 唯一约束：必须是 UNIQUE 的
  4. 主键：UNIQUE 跟 NOT NULL 的集合。一张表只能有一个主键，但主键可以由多个列组成。
  5. 外键：一张表可以有多个外键，外键能引用其他表的列。外键有多个策略：RESTRICT、CASCADE、NO ACTION、SET NULL、SET DEFAULT。
  6. 排他约束：使用指定操作符进行比较时，同一列不能有同样的值。
• 数据库有一些隐藏的系统列，用户不需要关心，只需要知道他们存在即可。
• postgres 内置了一些扩展，本节仅用里面最常用的 uuid-ossp 来实现插入数据时生成 uuid。